package app.netant.galgame.yuinya;

import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.db.sql.DBManager;
import cn.lily.com.db.sql.manager.DBOfflineManager;
import cn.lily.com.enviroment.Enviroment;
import cn.lily.com.file.excel.io.ExcelReader;
import cn.lily.com.file.excel.io.ExcelWriter;
import cn.lily.com.lang.Basic;

public class YuiNyaAnalyse {
	
	public static void cutFile(){
		
		ExcelWriter ew = new ExcelWriter("D:/mm_new.xlsx");
		ew.inst();
		ew.locateSheet(0);
		ExcelReader er = new ExcelReader("D:/mm.xlsx");
		er.locateSheet(0);
		DBManager dbManager = DBOfflineManager.getDBManager();
		
		int end = er.getRowNum()[1];
		
		for(int row=0;row<=end;row++){
			
			er.locateRow(row);ew.locateRow(row);
			
			String name = cutName(er.getCellValue(0));
			String href = er.getCellValue(1);
			
			if(name.indexOf("/")>0){
				name = name.substring(0,name.indexOf("/"));
			}
			if(name.indexOf("汉化")>0){
				name = name.substring(0,name.indexOf("汉化"));
			}
			
			String sqlString = "select RES_ID,GAME_ID,GAME_NAME,GAME_TYP,GAME_LANG,"
					+ "GAME_VISUAL,GAME_SIZE,GAME_SIZE_NUM,GAME_FACTORY,GAME_FIRST,"
					+ "GAME_INTRU,GEME_MEMO,GAME_NAME_PLUS,"
					+ "DISK_ID,GAME_STORE_PATH,GAME_TAGS,"
					+ "to_char(GAME_PRO_TIME,'yyyy-mm-dd hh24:mi:ss') GAME_PRO_TIME,"
					+ "to_char(GAME_CRE_TIME,'yyyy-mm-dd hh24:mi:ss') GAME_CRE_TIME "
			 + "from ${schema}.LEO_GALGAME_INFO "
			 + "where 1=1 and ( upper(GAME_NAME) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "' or upper(GAME_NAME_PLUS) like '" + Basic.toWhereLike(name.trim().toUpperCase()) + "') "
			 +" order by GAME_CRE_TIME desc";
			
			DataTable dataTable = dbManager.executeQueryTable(sqlString);
			
			String newName = "";			
			while(dataTable.next()){
				newName += ","+ dataTable.getString("GAME_NAME");
				if(dataTable.getString("GAME_NAME_PLUS") != null && !"".equals(dataTable.getString("GAME_NAME_PLUS") )){
					newName +=","+dataTable.getString("GAME_NAME_PLUS");
				}
			}
			
			ew.setCellValue(0,er.getCellValue(0));
			ew.setCellValue(1,name);
			ew.setCellValue(2,newName);
			ew.setCellValue(3,href);
			
		}
		
		ew.write();
		
	}
	
	public static String cutName(String name){
		
		StringBuffer sb = new StringBuffer();
		boolean flag = false;
		for(int i=0;i<name.length();i++){
			char active = name.charAt(i);
			
			if(active == '['){
				flag = false;
			}else if(active == ']'){
				flag = true;
			}else{
				if(flag){
					sb.append(active);
				}
			}
			
		}
		
		return sb.toString();
		
	}
	
	public static void main(String[] args) {
		Enviroment.createEnv();
		Enviroment.getEnv().getLogBean().beginLog();
		cutFile();
	}
}
